package com.cqggrw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.cqggrw.dao.Category;
import com.cqggrw.dao.NewsDaoI;
import com.cqggrw.domain.Img;
import com.cqggrw.domain.News;
import com.cqggrw.util.DBUtil;
import com.cqggrw.util.Utils;

public class NewsDaoImpl implements NewsDaoI<News> {

	public boolean add(News t) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "insert into tbl_news(name, type, addDate, addUser, comfrom, content, txtContent, ckbIsPass, ckbtop, cknishot, fileDes, fileName, falg) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, t.getName());
			ps.setInt(2, t.getType());
			ps.setString(3, t.getAddDate());
			ps.setString(4, t.getAddUser());
			ps.setString(5, t.getComfrom());
			ps.setString(6, t.getContent());
			ps.setString(7, t.getTxtContent());
			ps.setInt(8, t.getCkbIsPass());
			ps.setInt(9, t.getCkbtop());
			ps.setInt(10, t.getCknishot());
			ps.setString(11, t.getFileDes());
			ps.setString(12, t.getFilename());
			ps.setString(13, t.getFalg());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public boolean del(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "delete from tbl_news where id = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public boolean modify(News t) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "update tbl_news set name = ?, type = ?, addDate = ?, addUser = ?, comfrom = ?, content = ?, txtContent = ?, ckbIsPass = ?, ckbtop = ?, cknishot = ? WHERE ID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, t.getName());
			ps.setInt(2, t.getType());
			ps.setString(3, t.getAddDate());
			ps.setString(4, t.getAddUser());
			ps.setString(5, t.getComfrom());
			ps.setString(6, t.getContent());
			ps.setString(7, t.getTxtContent());
			ps.setInt(8, t.getCkbIsPass());
			ps.setInt(9, t.getCkbtop());
			ps.setInt(10, t.getCknishot());
			//ps.setString(11, t.getFileDes());
			//ps.setString(12, t.getFilename());
			ps.setInt(11, t.getId());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public News get(News t) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "select a.*, b.name typeName from tbl_news a, tbl_category b where a.type = b.id and a.id = ?;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t.getId());
			rs = ps.executeQuery();

			if (rs.next()) {
				t.setName(rs.getString("name"));
				t.setType(rs.getInt("type"));
				t.setTypeName(rs.getString("typeName"));
				t.setAddDate(rs.getString("addDate"));
				t.setAddUser(rs.getString("addUser"));
				t.setComfrom(rs.getString("comfrom"));
				t.setContent(rs.getString("content"));
				t.setTxtContent(rs.getString("txtContent"));
				t.setCkbIsPass(rs.getInt("ckbIsPass"));
				t.setCkbtop(rs.getInt("ckbtop"));
				t.setCknishot(rs.getInt("cknishot"));
				t.setFileDes(rs.getString("fileDes"));
				t.setFilename(rs.getString("fileName"));
				t.setFalg(rs.getString("falg"));
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return t;
	}
	
	public List<Img> getNewsImg(String falg) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Img> list = new ArrayList<Img>();
		Img img = null;
		
		String sql = "select * from tbl_img where falg = ?;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, falg);
			rs = ps.executeQuery();

			while (rs.next()) {
				img = new Img();
				img.setId(rs.getInt("id"));
				img.setName(rs.getString("name"));
				img.setDes(rs.getString("des"));
				img.setFalg(rs.getString("falg"));
				list.add(img);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}
	
	public List<Category> getCate() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Category t = null;
		List<Category> list = new ArrayList<Category>();
		
		String sql = "select * from tbl_category";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				t = new Category();
				t.setId(rs.getInt("id"));
				t.setName(rs.getString("name"));
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	public List<News> get() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		News t = null;
		List<News> list = new ArrayList<News>();
		
		String sql = "select a.*, b.name typeName from tbl_news a, tbl_category b where a.type = b.id;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				t = new News();
				t.setId(rs.getInt("id"));
				t.setName(rs.getString("name"));
				t.setType(rs.getInt("type"));
				t.setTypeName(rs.getString("typeName"));
				t.setAddDate(rs.getString("addDate"));
				t.setAddUser(rs.getString("addUser"));
				t.setComfrom(rs.getString("comfrom"));
				t.setContent(rs.getString("content"));
				t.setTxtContent(rs.getString("txtContent"));
				t.setCkbIsPass(rs.getInt("ckbIsPass"));
				t.setCkbtop(rs.getInt("ckbtop"));
				t.setCknishot(rs.getInt("cknishot"));
				t.setFileDes(rs.getString("fileDes"));
				t.setFilename(rs.getString("fileName"));
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	public List<News> getByTypeId(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		News t = null;
		List<News> list = new ArrayList<News>();
		
		String sql = "select a.*, b.name typeName from tbl_news a, tbl_category b where a.type = b.id and a.type = ? order by ckbtop desc, addDate desc limit 50;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {
				t = new News();
				t.setId(rs.getInt("id"));
				t.setName(rs.getString("name"));
				t.setTxtContent(rs.getString("txtContent"));
				t.setFilename(rs.getString("filename"));
				t.setAddDate(rs.getString("addDate"));
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	public List<News> get(News t, int i) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		News news = null;
		List<News> list = new ArrayList<News>();
		if(t.getName()!=null) {
			t.setName(t.getName().equals("")?null:t.getName());
		}
		if(t.getAddDate()!=null) {
			t.setAddDate(t.getAddDate().equals("")?null:t.getAddDate());
		}
		if(t.getTypeName()!=null) {
			t.setTypeName(t.getTypeName().equals("")?null:t.getTypeName());
		}
		
		String sql = "select a.*, b.name typeName from tbl_news a, tbl_category b where a.type = b.id and a.name like '%"+(t.getName()==null?"":t.getName())+"%' and a.addDate like '%"+(t.getAddDate()==null?"":t.getAddDate())+"%' and b.name like '%"+(t.getTypeName()==null?"":t.getTypeName())+"%' order by addDate desc limit ?, ?;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (t.getPage()-1)*t.getRows());
			ps.setInt(2, t.getRows());
			rs = ps.executeQuery();

			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setName(rs.getString("name"));
				news.setType(rs.getInt("type"));
				news.setTypeName(rs.getString("typeName"));
				news.setAddDate(rs.getString("addDate"));
				news.setAddUser(rs.getString("addUser"));
				news.setComfrom(rs.getString("comfrom"));
				news.setCkbIsPass(rs.getInt("ckbIsPass"));
				news.setCkbtop(rs.getInt("ckbtop"));
				news.setCknishot(rs.getInt("cknishot"));
				news.setFileDes(rs.getString("fileDes"));
				news.setFilename(rs.getString("fileName"));
				news.setFalg(rs.getString("falg"));
				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}
	
	public int getTotal(News t) {
		
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		
		if(t.getName()!=null) {
			System.out.println(222);
			t.setName(t.getName().equals("")?null:t.getName());
		}
		if(t.getAddDate()!=null) {
			System.out.println(44);
			t.setAddDate(t.getAddDate().equals("")?null:t.getAddDate());
		}
		if(t.getTypeName()!=null) {
			System.out.println(66);
			t.setTypeName(t.getTypeName().equals("")?null:t.getTypeName());
		}
		
		String sql = "select count(*)from tbl_news a, tbl_category b where a.type = b.id and a.name like '%"+(t.getName()==null?"":t.getName())+"%' and a.addDate like '%"+(t.getAddDate()==null?"":t.getAddDate())+"%' and b.name like '%"+(t.getTypeName()==null?"":t.getTypeName())+"%' order by addDate desc;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return total;
	}

	public boolean addImg(Img img) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "insert into tbl_img(des,name,falg) values(?,?,?);";
		
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, img.getDes());
			ps.setString(2, img.getName());
			ps.setString(3, img.getFalg());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public List<News> searchByName(String name, Integer page) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		News news = null;
		List<News> list = new ArrayList<News>();
		
		String sql = "select a.*, b.name typeName from tbl_news a, tbl_category b where a.type = b.id and a.content like ? order by id desc limit ?, 23;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			ps.setInt(2, (page-1)*23);
			rs = ps.executeQuery();

			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setName(rs.getString("name"));
				news.setType(rs.getInt("type"));
				news.setTypeName(rs.getString("typeName"));
				news.setAddDate(rs.getString("addDate"));
				list.add(news);
			}
		} catch (Exception e) {
			Utils.log(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	public int countForSearch(String name) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int total = 0;
		
		String sql = "select count(*) from tbl_news where content like ?";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			rs = ps.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (Exception e) {
			Utils.log(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return total;
	}

	public boolean saveMessage(News t) {
		return false;
	}

	public boolean updateNewsImg(News t) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "update tbl_news set fileDes = ?, fileName = ? WHERE ID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, t.getFileDes());
			ps.setString(2, t.getFilename());
			ps.setInt(3, t.getId());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public boolean modifyImg(Img img) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "update tbl_img set des = ?, name = ? WHERE ID = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, img.getDes());
			ps.setString(2, img.getName());
			ps.setInt(3, img.getId());
			
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public boolean delNewsImg(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "delete from tbl_img where id = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1;
	}

	public List<News> getListHasImgByType(Integer id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		News t = null;
		List<News> list = new ArrayList<News>();
		
		String sql = "SELECT * FROM tbl_news WHERE fileName IS NOT NULL AND TYPE = ?;";
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {
				t = new News();
				t.setId(rs.getInt("id"));
				t.setName(rs.getString("name"));
				t.setTxtContent(rs.getString("txtContent"));
				t.setFilename(rs.getString("filename"));
				t.setAddDate(rs.getString("addDate"));
				list.add(t);
			}
		} catch (Exception e) {
			Utils.log(e);
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

}
